<?php
// ********************************************
// 
// Каталог автомобилей
//
// ********************************************

require_once( "tools.class.php" );
require_once( "tables.class.php" );    
require_once( "datadefine.class.php" );

class Cars extends tables 
{
    private static $error = '<b>Извините. </b>На сайте возникли трудности технического характера.<br>Обратитесь за помощью к администратору сайта.';

    public function GetPage()
    {
        $data = new jqGridData();

        while( true )
        {
			if( !isset( $_SESSION['cust_id'] ) || !$_SESSION['cust_id'] ) {
				$data->userdata->errmess = "Вы не выполнили вход в систему<br>или Ваша сессия закончилась по таймауту."; 
				$data->userdata->errcode = 999; break;
			}
			$cust_id =  $_SESSION['cust_id'];

            if( $this->db == NULL ) {
                $data->userdata->errcode = 1;
                $data->userdata->errmess = self::$error;
                break;
            }
            $db = $this->db;
            
// Проверяем данные
            $page    = ( isset($_REQUEST["page"]) && $_REQUEST["page"] > 0 ) ? $_REQUEST["page"] : 1;
            $limit   = ( isset($_REQUEST["rows"]) && $_REQUEST["rows"] > 0 ) ? $_REQUEST["rows"] : 1;
            $sidx    = ( isset($_REQUEST["sidx"]) && $_REQUEST["sidx"] != '' ) ? $_REQUEST["sidx"] : 'car_id';
            $sord    = ( isset($_REQUEST["sord"]) && strtolower($_REQUEST["sord"]) == 'desc' ) ? 'DESC' : 'ASC';

            switch ($sidx)
            {
                case "city_id":     $sidx = "cars.city_id"; break;
                case "vend_name":   $sidx = "vend_name"; break;
                case "model_name":  $sidx = "model_name"; break;
                case "modif_name":  $sidx = "modif_name"; break;
                case "p_date":      $sidx = "p_date"; break;
                case "km_count":    $sidx = "km_count"; break;
                case "price":       $sidx = "price"; break;
                case "comment":     $sidx = "comment"; break;
                default: $sidx = "cars.car_id"; break;
            }
                    
            $whereclaus = " WHERE cust_id=" . $cust_id;

// Получаем количество записей с учетом фильтра
            
            $sqlreq = "SELECT COUNT(*) AS count FROM cars WHERE owner_id=" . $cust_id;
                
            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();

// Пустая таблица
            if ($count == 0) break;

// Рассчитаем сколько всего страниц займут данные в БД
            if( $count > 0 && $limit > 0) {
                $total_pages = ceil($count/$limit);
            } else {
                $total_pages = 0;
            }                  
 
            if ($page > $total_pages) $page=$total_pages;                  
 
            $start = $limit*$page - $limit;                  
            if($start < 0) $start = 0;                  
 
// Формируем запрос
            $sqlreq =
				"SELECT cars.car_id AS car_id, city.city_name AS city_name, cars.vend_id AS vend_id, ".
				"cars.model_id AS model_id, cars.modif_id AS modif_id, ".
				"cars.p_date AS p_date, cars.km_count AS km_count, ".
				"cars.price AS price, cars.comment AS comment, ".
                "vdic.vend_name AS vend_name, mdic.model_name AS model_name, ".
				"lng_text.text_text AS modif_name ".
                "FROM cars ".
				"INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id ".
				"INNER JOIN models AS mdic ON mdic.model_id = cars.model_id ".
				"INNER JOIN modifics AS tdic ON tdic.typ_id = cars.modif_id ".
                "LEFT OUTER JOIN city AS city ON city.city_id = cars.city_id ".
                "LEFT OUTER JOIN cdesign AS lng_des ON tdic.full_cdes_id = lng_des.cdes_id ".
                "LEFT OUTER JOIN descriptions AS lng_text ON lng_des.text_id = lng_text.text_id ".
                "WHERE owner_id=".$cust_id." ORDER BY ".$sidx." ".$sord." LIMIT ".$start.", ".$limit;
            
            $recset = $db->query($sqlreq);                  
            
// Формирование объекта для возврата
            $data->page      = $page;
            $data->total     = $total_pages;
            $data->records   = $count;
        
// Где хранятся фото
			$upload_dir = dirname($_SERVER['SCRIPT_FILENAME']).'/photo/'.$cust_id.'/img/';

// Строки данных для таблицы
            $i = 0;
            while($row = $recset->fetch(PDO::FETCH_ASSOC)) {
                $data->rows[$i]['id'] = $row['car_id'];
                $data->rows[$i]['cell'] = array(
                        $row['car_id'],
                        $row['city_name'],
                        $row['vend_id'],
                        $row['vend_name'],
                        $row['model_id'],
                        $row['model_name'],
                        $row['modif_id'],
                        $row['modif_name'],
						is_file($upload_dir.$row['car_id'].'.jpg') ? 'photo/'.$cust_id.'/tbl/'.$row['car_id'].'.jpg' : '',
                        $row['p_date'],
						$row['km_count'],
                        $row['price'],
                        $row['comment']
                );
                $i++;
            }
            break;
        }
        
// Отдаем результат
        header("Content-type: text/script;charset=utf-8");
        echo json_encode($data);
        return;
    }

    
    public function Insert()
    {
        $car_id   = isset($_REQUEST["id"]) ? $_REQUEST["id"] : '';
        $city_id  = isset($_REQUEST["city_id"]) ? $_REQUEST["city_id"] : '';
        $vend_id  = isset($_REQUEST["vend_id"]) ? $_REQUEST["vend_id"] : '';
        $model_id = isset($_REQUEST["model_id"]) ? $_REQUEST["model_id"] : '';
        $modif_id = isset($_REQUEST["modif_id"]) ? $_REQUEST["modif_id"] : '';
        $p_date   = isset($_REQUEST["p_date"]) ? $_REQUEST["p_date"] : '';
        $km_count = isset($_REQUEST["km_count"]) ? $_REQUEST["km_count"] : '';
        $price    = isset($_REQUEST["price"])  ? $_REQUEST["price"]  : '';
        $comment  = isset($_REQUEST["comment"]) ? $_REQUEST["comment"] : '';

        $errmess = "";
		$errcode = 0;
        
        while (true)
        {
			if( !isset( $_SESSION['cust_id'] ) || !$_SESSION['cust_id'] ) {
				$errmess = "Вы не выполнили вход в систему<br>или Ваша сессия закончилась по таймауту."; 
				$errcode = 999; break;
			}
			$cust_id =  $_SESSION['cust_id'];

            if ($car_id != "_empty" || !is_numeric($city_id) || !is_numeric($vend_id) ||  !is_numeric($model_id) || 
                !is_numeric($modif_id) || !is_numeric($p_date) || !is_numeric($km_count) || !is_numeric($price) ) {
                $errmess = "Нарушение формата данных";
				$errcode = 2;
                break;
            }

             if( $this->db == NULL ) {
                $errmess = self::$error;
				$errcode = 1;
                break;
            }
            $db = $this->db;
            
// Проверяем существование
            $sqlreq = "SELECT COUNT(*) AS count FROM cars WHERE (".
                " owner_id=".$db->quote($cust_id)." AND modif_id=".$db->quote($modif_id).
                " AND p_date=".$db->quote($p_date)." AND km_count=".$db->quote($km_count).
				" AND price=".$db->quote($price)." )";

            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            if ($count > 0) {
                $errmess = "Среди Ваших предложений уже присутствует автомобиль с указанными атрибутами.";
				$errcode = 2;
                break;
            }
            
            $sqlreq = "INSERT INTO cars ( owner_id, city_id, vend_id, model_id, modif_id, p_date, ".
				"km_count, price, comment, mtime ) VALUES (".
                $db->quote($cust_id).", ".$db->quote($city_id).", ".$db->quote($vend_id).", ".$db->quote($model_id).", ".
				$db->quote($modif_id).", ".$db->quote($p_date).", ".$db->quote($km_count).", ".
				$db->quote($price).", ".$db->quote($comment).", ".time().")";

            if( $db->exec($sqlreq) === false ){
                $errmess = self::$error;
				$errcode = 1;
                break;
            }

            break;
        }

        Tools::json_response( $errmess, $errcode );            
        return;
    }

    
    public function Update()
    {
        $car_id   = isset($_REQUEST["id"]) ? $_REQUEST["id"] : '';
        $city_id  = isset($_REQUEST["city_id"]) ? $_REQUEST["city_id"] : '';
        $vend_id  = isset($_REQUEST["vend_id"]) ? $_REQUEST["vend_id"] : '';
        $model_id = isset($_REQUEST["model_id"]) ? $_REQUEST["model_id"] : '';
        $modif_id = isset($_REQUEST["modif_id"]) ? $_REQUEST["modif_id"] : '';
        $p_date   = isset($_REQUEST["p_date"]) ? $_REQUEST["p_date"] : '';
        $km_count = isset($_REQUEST["km_count"]) ? $_REQUEST["km_count"] : '';
        $price    = isset($_REQUEST["price"])  ? $_REQUEST["price"]  : '';
        $comment  = isset($_REQUEST["comment"]) ? $_REQUEST["comment"] : '';

        $errmess = "";
		$errcode = 0;
        
        while (true)
        {
            if (!is_numeric($car_id) || !is_numeric($city_id) || !is_numeric($vend_id) ||  !is_numeric($model_id) || 
                !is_numeric($modif_id) || !is_numeric($p_date) || !is_numeric($km_count) || !is_numeric($price) ) {
                $errmess = "Нарушение формата данных";
				$errcode = 2;
                break;
            }

			if( !isset( $_SESSION['cust_id'] ) || !$_SESSION['cust_id'] ) {
				$errmess = "Вы не выполнили вход в систему<br>или Ваша сессия закончилась по таймауту."; 
				$errcode = 999; break;
			}
			$cust_id =  $_SESSION['cust_id'];

             if( $this->db == NULL ) {
                $errmess = self::$error;
				$errcode = 1;
                break;
            }
            $db = $this->db;
            
            // Проверяем существование
            $sqlreq = "SELECT COUNT(*) AS count FROM cars WHERE (".
                " owner_id=".$db->quote($cust_id)." AND modif_id=".$db->quote($modif_id).
                " AND p_date=".$db->quote($p_date)." AND km_count=".$db->quote($km_count).
				" AND price=".$db->quote($price)." AND car_id != ".$db->quote($car_id)." )";

            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            if ($count > 0) {
                $errmess = "В справочнике уже присутствует автомобиль с указанными атрибутами.";
				$errcode = 2;
                break;
            }
            
            $sqlreq = "UPDATE cars SET ".
                "city_id=".$db->quote($city_id).", ".
                "vend_id=".$db->quote($vend_id).", ".
                "model_id=".$db->quote($model_id).", ".
                "modif_id=".$db->quote($modif_id).", ".
                "p_date=".$db->quote($p_date).", ".
                "km_count=".$db->quote($km_count).", ".
                "price=".$db->quote($price).", ".
                "comment=".$db->quote($comment).", ".
				"mtime=".time()." ".
                "WHERE car_id=".$db->quote($car_id);

            if( $db->exec($sqlreq) === false ){
                $errmess = self::$error;
				$errcode = 1;
                break;
            }

            break;
        }

        Tools::json_response( $errmess, $errcode );            
        return;
    }


    public function Delete()
    {
        $errmess = "";
		$errcode = 0;
		
        $car_ids = (isset($_REQUEST['id']) && $_REQUEST['id'] != '') ?
            explode(",", $_REQUEST["id"], 100) : array();

        while( true ) {
            if (!Tools::is_numeric_array( $car_ids )) {
                $errmess = "Нарушение формата данных.";
				$errcode = 2;
                break;
            }
			if( !isset( $_SESSION['cust_id'] ) || !$_SESSION['cust_id'] ) {
				$errmess = "Вы не выполнили вход в систему<br>или Ваша сессия закончилась по таймауту."; 
				$errcode = 999; break;
			}
			$cust_id =  $_SESSION['cust_id'];

            $car_id_list = implode(',', $car_ids);
            
            if( $this->db == NULL ) {
                $errmess = self::$error;
				$errcode = 1;
                break;
            }
            $db = $this->db;
        
            $sqlreq =
//               "START TRANSACTION;".
               "DELETE FROM cars WHERE car_id IN (".$car_id_list.")";
            if( $db->exec($sqlreq) === false ){
//                $db->exec("ROLLBACK;");
                $errmess = self::$errmess;
				$errcode = 1;
                break;
            }
			$img_dir = dirname($_SERVER['SCRIPT_FILENAME']).'/photo/'.$cust_id.'/img/';
			$tbl_dir = dirname($_SERVER['SCRIPT_FILENAME']).'/photo/'.$cust_id.'/tbl/';
			foreach ( $car_ids as $car_id ) {
				@unlink( $img_dir . $car_id . '.jpg' );
				@unlink( $tbl_dir . $car_id . '.jpg' );
			}

//            else {
//                $this->db->exec("COMMIT;");
//            }

            break;
        }

        Tools::json_response( $errmess, $errcode, $img_dir . $car_id . '.jpg' );            
        return;
    }


    // 
    // Каталог автомобилей
    //
    public function Find()
    {
        $data = new jqGridData();

        while( true )
        {
            if( $this->db == NULL ) {
                $data->userdata->errcode = 1;
                $data->userdata->errmess = self::$error;
                break;
            }
            $db = $this->db;
            
// Проверяем данные
            $page    = ( isset($_REQUEST["page"]) && $_REQUEST["page"] > 0 ) ? $_REQUEST["page"] : 1;
            $limit   = ( isset($_REQUEST["rows"]) && $_REQUEST["rows"] > 0 ) ? $_REQUEST["rows"] : 1;
            $sidx    = ( isset($_REQUEST["sidx"]) && $_REQUEST["sidx"] != '' ) ? $_REQUEST["sidx"] : 'car_id';
            $sord    = ( isset($_REQUEST["sord"]) && strtolower($_REQUEST["sord"]) == 'desc' ) ? 'DESC' : 'ASC';
            $search  = ( isset($_REQUEST["_search"]) && strtolower($_REQUEST["_search"]) == 'true') ? true : false;

            switch ($sidx)
            {
                case "city_name":   $sidx = "city.city_name"; break;
                case "vend_name":   $sidx = "vdic.vend_name"; break;
                case "model_name":  $sidx = "mdic.model_name"; break;
                case "p_date":      $sidx = "cars.p_date";   break;
                case "km_count":    $sidx = "cars.km_count"; break;
                case "price":       $sidx = "cars.price"; break;
                default: $sidx = "cars.car_id"; break;
            }
                
// Фильтр
            $whc = array();
            if( $search )
            {
                if (isset($_REQUEST["car_id"]) && is_numeric($_REQUEST["car_id"]) ) {
                    $whc[] = "cars.car_id=" . $db->quote($_REQUEST["car_id"]);
                }
                if(isset($_REQUEST["vend_id"]) && is_numeric($_REQUEST["vend_id"]) ) {
                    $whc[] = "cars.vend_id=" . $db->quote($_REQUEST["vend_id"]);
                }
                if(isset($_REQUEST["model_id"]) && is_numeric($_REQUEST["model_id"]) ) {
                    $whc[] = "cars.model_id=" . $db->quote($_REQUEST["model_id"]);
                }
                if(isset($_REQUEST["city_id"]) && is_numeric($_REQUEST["city_id"]) && $_REQUEST["city_id"] != 0) {
                    $whc[] = "city.city_id=" . $db->quote($_REQUEST["city_id"]);
                }
                if(isset($_REQUEST["vend_name"]) && $_REQUEST["vend_name"] != "") {
                    $whc[] = "vdic.vend_name LIKE " . $db->quote("%".strip_tags($_REQUEST["vend_name"])."%");
                }
                if(isset($_REQUEST["model_name"]) && $_REQUEST["model_name"] != "") {
                    $whc[] = "mdic.model_name LIKE " . $db->quote("%".strip_tags($_REQUEST["model_name"])."%");
                }
                if(isset($_REQUEST["lp_date"]) && is_numeric($_REQUEST["lp_date"]) ) {
                    $whc[] = "cars.p_date >= " . $db->quote($_REQUEST["lp_date"]);
                }
                if(isset($_REQUEST["hp_date"]) && is_numeric($_REQUEST["hp_date"]) ) {
                    $whc[] = "cars.p_date <= " . $db->quote($_REQUEST["hp_date"]);
                }
                if(isset($_REQUEST["lkm_count"]) && is_numeric($_REQUEST["lkm_count"]) ) {
                    $whc[] = "cars.km_count >= " . $db->quote($_REQUEST["lkm_count"]);
                }
                if(isset($_REQUEST["hkm_count"]) && is_numeric($_REQUEST["hkm_count"]) ) {
                    $whc[] = "cars.km_count <= " . $db->quote($_REQUEST["hkm_count"]);
                }
                if (isset($_REQUEST["lprice"]) && $_REQUEST["lprice"] != "") {
                    $whc[] = "cars.price >= ".$db->quote(str_replace(',', '.', $_REQUEST["lprice"]));
                }
                if (isset($_REQUEST["hprice"]) && $_REQUEST["hprice"] != "") {
                    $whc[] = "cars.price <= ".$db->quote(str_replace(',', '.', $_REQUEST["hprice"]));
                }
            }
            $whereclaus = (count($whc) != 0 ) ? "WHERE (".implode(" AND ", $whc).") " : "";
            unset($whc);
                            
            $joins = 
				"INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id ".
				"INNER JOIN models AS mdic ON mdic.model_id = cars.model_id ".
				"INNER JOIN modifics AS tdic ON tdic.typ_id = cars.modif_id ".
				"INNER JOIN customers AS cust ON cust.cust_id = cars.owner_id ".
                "LEFT OUTER JOIN city AS city ON city.city_id = cars.city_id ".
                "LEFT OUTER JOIN cdesign AS lng_des ON tdic.full_cdes_id = lng_des.cdes_id ".
                "LEFT OUTER JOIN descriptions AS lng_text ON lng_des.text_id = lng_text.text_id ";
                
// Получаем количество записей с учетом фильтра
            $sqlreq = "SELECT COUNT(*) AS count FROM cars ".$joins.$whereclaus;
            
            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            
// Пустая таблица
            if ($count == 0) break;
            
// Рассчитаем сколько всего страниц займут данные в БД
            if( $count > 0 && $limit > 0) {
                $total_pages = ceil($count/$limit);
            } else {
                $total_pages = 0;
            }                  
            
            if ($page > $total_pages) $page=$total_pages;                  

            $start = $limit*$page - $limit;                  
            if($start < 0) $start = 0;                  
            
 // Формируем запрос
			$sqlreq = 
				"SELECT cars.car_id AS car_id, cars.owner_id AS owner_id, city.city_name AS city_name, ".
				"cars.vend_id AS vend_id, cars.model_id AS model_id, cars.modif_id AS modif_id, ".
				"cars.p_date AS p_date, cars.km_count AS km_count, ".
				"cars.price AS price, cars.comment AS comment, ".
                "vdic.vend_name AS vend_name, mdic.model_name AS model_name, ".
				"cust.phone AS phone, cust.uname AS owner_name, lng_text.text_text AS modif_name ".
                "FROM cars ".$joins.$whereclaus."ORDER BY ".$sidx." ".$sord." LIMIT ".$start.", ".$limit;
                  
            $recset = $db->query($sqlreq);                  
            
// Формирование объекта для возврата
            $data->page      = $page;
            $data->total     = $total_pages;
            $data->records   = $count;
            
// Где хранятся фото
			$root_dir = dirname($_SERVER['SCRIPT_FILENAME']).'/';

// Строк и данных для таблицы
            $i = 0;
            while($row = $recset->fetch(PDO::FETCH_ASSOC)) {
				$tbl_url = 'photo/'.$row['owner_id'].'/tbl/'.$row['car_id'].'.jpg';
				$img_url = 'photo/'.$row['owner_id'].'/img/'.$row['car_id'].'.jpg';
				
                $data->rows[$i]['id'] = $row['car_id'];
                $data->rows[$i]['cell'] = array(
                        $row['car_id'],
                        $row['city_name'],
                        $row['modif_name'],
                        $row['p_date'],
                        $row['km_count'],
                        $row['price'],
                        $row['comment'],
						is_file($root_dir.$tbl_url)	? $tbl_url : 'photo/0/tbl/0.jpg',
						is_file($root_dir.$img_url) ? $img_url : 'photo/0/img/0.jpg',
                        $row['owner_id'],
                        $row['owner_name'],
                        $row['phone']
                );
                $i++;
            }
            break;
        }
        
// Отдаем результат
        header("Content-type: text/script;charset=utf-8");
        echo json_encode($data);
        return;
    }

    public function GetList() 
    {
        return;
    }

    public function GetFirstLast()
    {
		$errmess = "";
		$errcode = 0;
		$data = array();

        while( true )
        {
            if( $this->db == NULL ) {
                $errcode = 1;
                $errmess = self::$error;
                break;
            }
            $db = $this->db;
            
// Проверяем данные
            $amount  = ( isset($_REQUEST["amount"]) && is_numeric($_REQUEST["amount"]) && $_REQUEST["amount"] > 0 ) ? $_REQUEST["amount"] : 10;
            $sord    = ( isset($_REQUEST["sord"]) && strtolower($_REQUEST["sord"]) == 'desc' ) ? 'DESC' : 'ASC';

// Формируем запрос
            $sqlreq =
				"SELECT cars.car_id AS car_id, cars.price AS price, ".
                "cars.owner_id AS owner_id, lng_text.text_text AS modif_name ".
                "FROM cars ".
				"INNER JOIN modifics AS tdic ON tdic.typ_id = cars.modif_id ".
                "LEFT OUTER JOIN cdesign AS lng_des ON tdic.full_cdes_id = lng_des.cdes_id ".
                "LEFT OUTER JOIN descriptions AS lng_text ON lng_des.text_id = lng_text.text_id ".
                "ORDER BY mtime ".$sord." LIMIT 0, ".$amount;
            
            $recset = $db->query($sqlreq);                  
            
// Где хранятся фото
			$photo_dir = dirname($_SERVER['SCRIPT_FILENAME']).'/photo';

// Корневой url
			$photo_url = (isset($_SERVER['HTTPS']) ? 'https://' : 'http://').
				(isset($_SERVER['REMOTE_USER']) ? $_SERVER['REMOTE_USER'].'@' : '').
				(isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : ($_SERVER['SERVER_NAME'].
				(isset($_SERVER['HTTPS']) && $_SERVER['SERVER_PORT'] === 443 ||
				$_SERVER['SERVER_PORT'] === 80 ? '' : ':'.$_SERVER['SERVER_PORT']))).
				substr($_SERVER['SCRIPT_NAME'],0, strrpos($_SERVER['SCRIPT_NAME'], '/')).'/photo';
				
// Строки данных для таблицы
            while($row = $recset->fetch(PDO::FETCH_ASSOC)) {
				$owner_id = $row['owner_id'];
				$car_id = $row['car_id'];
				
				$file_path = $photo_dir.'/'.$owner_id.'/tbl/'.$car_id.'.jpg';
				if( !is_file($file_path) ) continue;
				
                $data[] = array( 
						'owner_id'   => $owner_id, 
                        'car_id'     => $car_id,
                        'price'      => $row['price'],
                        'modif_name' => $row['modif_name'],
						'tbl'		 => $photo_url.'/'.$owner_id.'/tbl/'.$car_id.'.jpg'
                );
            }
            break;
        }
        
// Отдаем результат
        header("Content-type: text/script;charset=utf-8");
        Tools::json_response( $errmess, $errcode, $data );            
        return;
    }
    
} // End of Cars Catalogue 

?>
